-- 工单：大数据-八维保险数据挖掘-05-财产保险成本费用相关
-- 1.3 寿险 ODS 层建表
CREATE DATABASE IF NOT EXISTS life_ods;
USE life_ods;

DROP TABLE IF EXISTS ods_policy;
CREATE EXTERNAL TABLE ods_policy (
    policy_no           STRING COMMENT '保单号',
    product_type        STRING COMMENT '险种类型',
    total_premium       DECIMAL(18,2) COMMENT '总保费 PL-I-L0085',
    start_date          DATE,
    end_date            DATE,
    org_id              STRING,
    created_at          TIMESTAMP
) COMMENT '保单原始表'
PARTITIONED BY (dt STRING)
STORED AS ORC;

-- 再保分出
DROP TABLE IF EXISTS ods_ri_out;
CREATE EXTERNAL TABLE ods_ri_out (
    ri_no          STRING,
    policy_no      STRING,
    ri_premium     DECIMAL(18,2) COMMENT '分出保费 RI-I-L0099',
    ri_date        DATE
) PARTITIONED BY (dt STRING)
STORED AS ORC;